package com.layer.cms.generate.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.base.CaseFormat;
import com.layer.cms.generate.mapper.CodeGenerateMapper;
import com.layer.cms.generate.pojo.CodeGenerate;
import com.layer.cms.generate.pojo.CodeGenerateConfig;
import com.layer.cms.generate.service.CodeGenerateService;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import java.io.UnsupportedEncodingException;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Service
public class CodeGenerateServiceImpl extends ServiceImpl<CodeGenerateMapper, CodeGenerate> implements CodeGenerateService {

	@Value("${spring.datasource.url}")
	private String url;

	@Value("${spring.datasource.driver-class-name}")
	private String driver;

	@Value("${spring.datasource.username}")
	private String username;

	@Value("${spring.datasource.password}")
	private String password;

	@Override
	public List<CodeGenerate> queryAll() {

		List<CodeGenerate> list = this.list();

		if (list.size() == 0) {
			return null;
		}

		return list;
	}

	public List<CodeGenerate> queryTableList() {

		// 根据 四要素 获取对应库中所有表信息(且排除掉代码生成表[sys_code_generate][sys_code_generate_config])
		Connection connection = getConnection();
		if (null == connection) {
			return null;
		}

		// 查询所有的表信息
		return queryTableList(connection);
	}

	private List<CodeGenerate> queryTableList(Connection connection) {

		List<CodeGenerate> list = new ArrayList<>();
		try {

			DatabaseMetaData metaData = connection.getMetaData();
			ResultSet tableList = metaData.getTables(connection.getCatalog(), null, null, null);
			while (tableList.next()) {

				CodeGenerate table = new CodeGenerate();

				// 获取表名
				String tableName = tableList.getString("TABLE_NAME");
				if (tableName.equalsIgnoreCase("sys_code_generate") || tableName.equalsIgnoreCase("sys_code_generate_config")) {
					continue;
				}

				// 获取表描述
				String remark = tableRemark(connection, tableName);

				table.setRemark(remark);
				table.setTableName(tableName);

				list.add(table);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			try {

				if (null != connection) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		return list;
	}

	// 获取表的描述
	private String tableRemark(Connection connection, String tableName) {

		try {

			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + tableName);
			if (rs != null && rs.next()) {

				// DDL 语句
				String create = rs.getString(2);
				String remark = parse(create);

				return remark;
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return "";
	}

	// 解析 DDL 语句获得 comment 后的字符
	private String parse(String all) {

		String comment = null;
		int index = all.indexOf("COMMENT='");
		if (index < 0) {
			return "";
		}
		comment = all.substring(index + 9);
		comment = comment.substring(0, comment.length() - 1);
        comment = new String(comment.getBytes(StandardCharsets.UTF_8));
        return comment;
	}

	// 获得数据库链接
	private Connection getConnection() {

		Connection connection = null;
		try {

			Class.forName(driver);
			connection = DriverManager.getConnection(url, username, password);

		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}

	@Override
	public void insertOrUpdate(CodeGenerate generate) {

		System.out.println(generate.toString());
		// 根据表名查询
		CodeGenerate old = this.getOne(new LambdaQueryWrapper<CodeGenerate>().eq(CodeGenerate::getTableName, generate.getTableName()));
		if (null == old) {

			// 新建
			// 处理类名
			generate = dealWithClassName(generate);

			// 处理描述
			generate = dealWithRemark(generate);

			this.save(generate);
		} else {

			// 修改
			generate = dealWithClassName(generate);
			generate = dealWithRemark(generate);
			generate.setId(old.getId());

			this.updateById(generate);
		}
	}

	// 处理描述
	private CodeGenerate dealWithRemark(CodeGenerate generate) {

		if (ObjectUtils.isEmpty(generate.getRemark())) {

			// 查询表的描述
			Connection connection = getConnection();

			// 获取表描述
			String remark = tableRemark(connection, generate.getTableName());

			generate.setRemark(remark);
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return generate;
	}

	// 根据表名获得类名
	private CodeGenerate dealWithClassName(CodeGenerate generate) {

		if (ObjectUtils.isEmpty(generate.getClassName())) {

			String tmp;
			if (generate.getTablePrefix() == 1) {

				tmp = generate.getTableName().substring(generate.getTableName().indexOf("_") + 1);
			} else {
				tmp = generate.getTableName();
			}
			tmp = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.UPPER_CAMEL, tmp);

			generate.setClassName(tmp);
		}

		return generate;
	}

	// 通过表名查询字段信息
	@Override
	public List<CodeGenerateConfig> config(String table) {

		// 通过表名查询表基本配置信息
		CodeGenerate generate = this.getOne(new LambdaQueryWrapper<CodeGenerate>().eq(CodeGenerate::getTableName, table));

		List<CodeGenerateConfig> list = new ArrayList<>();
		Connection connection = getConnection();
		try {

			DatabaseMetaData metaData = connection.getMetaData();

			// 获得表中的所有列
			ResultSet columns = metaData.getColumns(connection.getCatalog(), null, table, null);

			// 获取主键
			ResultSet primaryKeys = metaData.getPrimaryKeys(connection.getCatalog(), null, table);

			while (columns.next()) {

				// 字段名
				String columnName = columns.getString("COLUMN_NAME");

				// 字段类型
				String columnType = columns.getString("TYPE_NAME");

				// 字段描述
				String remarks = columns.getString("REMARKS");

				// 属性名
				String propertyName = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.UPPER_CAMEL, columnName);

				// 是否是主键
				Integer isPrimary = 0;

				// 遍历主键判断列是否是主键
				while (primaryKeys.next()) {

					String pkName = primaryKeys.getString("COLUMN_NAME");
					if (columnName.equals(pkName)) {
						isPrimary = 1;
					}
					primaryKeys.first();
				}

				// 构建实体类
				CodeGenerateConfig config = CodeGenerateConfig.builder().columnName(columnName).type(columnType).remark(remarks).propertyName(propertyName).isPrimary(isPrimary).build();

				config.setColumnType(columnType);
				// 处理 数据库数据类型 和 JavaBean 数据类型的转换
				config.setType(getTypeName(columnType));
				config.setCodeGenId(generate.getId());

				list.add(config);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	private String getTypeName(String typeName) {

		if (typeName.equalsIgnoreCase("int") || typeName.equalsIgnoreCase("tinyint")) {

			return "Integer";
		} else if (typeName.equalsIgnoreCase("bigint")) {

			return "String";
		} else if (typeName.equalsIgnoreCase("varchar") || typeName.equalsIgnoreCase("text") || typeName.equalsIgnoreCase("char")) {

			return "String";
		} else if (typeName.equalsIgnoreCase("datetime") || typeName.equalsIgnoreCase("timestamp") || typeName.equalsIgnoreCase("date")) {

			return "Date";
		} else if (typeName.equalsIgnoreCase("time")) {

			return "Date";
		} else if (typeName.equalsIgnoreCase("BIT")) {

			return "Boolean";
		} else if (typeName.equalsIgnoreCase("DOUBLE")) {

			return "Double";
		}
		return "String";
	}

}
